BEGIN;

-- EXTENSIONS --

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- TABLES --


CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    comments_enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INT NOT NULL,
    parent_id INT,
    content TEXT NOT NULL CHECK (LENGTH(content) <= 2000),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_post
        FOREIGN KEY(post_id)
            REFERENCES posts(id)
            ON DELETE CASCADE,
    CONSTRAINT fk_parent
        FOREIGN KEY(parent_id)
            REFERENCES comments(id)
            ON DELETE CASCADE
);

COMMIT;